/*
 * Created by kopper on 31.01.15 18:43
 * (C) Copyright 2015 kopperek@gmail.com
 *
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the GNU Lesser General Public License
 * (LGPL) version 2.1 which accompanies this distribution, and is available at
 * http://www.gnu.org/licenses/lgpl-2.1.html
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 */

package app.android.kopper.foramemo.util;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

public class ForaMemoDbHelper extends SQLiteOpenHelper {

    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "ForaMemo.db";

    public static Long normalizeDate(Date date) {
        int toDaysValue=1000*60*60*24;
        return date==null?null:Long.valueOf(date.getTime()/toDaysValue*toDaysValue);
    }

    public long selectWordsCount(SQLiteDatabase readableDb, String where, String... params) {
        String selectQuery="select count(1) from "+FeedEntry.TABLE_NAME ;
        if(where!=null) {
            selectQuery=selectQuery+" where "+where;
        }
        LogProxy.d("SelectCountQuery: "+selectQuery);
        LogProxy.d("QueryParams: "+(params==null?"null":Arrays.asList(params)));
        Cursor cursor = readableDb.rawQuery(selectQuery, params);
        long result = 0;
        if(cursor.moveToNext()) {
            result = cursor.getLong(0);
        }
        cursor.close();
        return(result);
    }

    public Set<Word> selectWords(SQLiteDatabase readableDb, String where, Long limit, String... params) {

        String selectQuery="select "+
                FeedEntry._ID + COMMA_SEP +
                FeedEntry.COLUMN_NAME_WORD + COMMA_SEP +
                FeedEntry.COLUMN_NAME_R1 + COMMA_SEP +
                FeedEntry.COLUMN_NAME_R2 + COMMA_SEP +
                FeedEntry.COLUMN_NAME_EP + COMMA_SEP +
                FeedEntry.COLUMN_NAME_REP_NO + COMMA_SEP +
                FeedEntry.COLUMN_NAME_ENABLE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_FIRST_OCCURRENCE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_LAST_ANSWER + COMMA_SEP +
                FeedEntry.COLUMN_NAME_NEXT_OCCURRENCE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_NEXT_CALCULATED_OCCURRENCE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_ADDED +
                " from "+FeedEntry.TABLE_NAME ;
        if(where!=null) {
            selectQuery=selectQuery+" where "+where;
        }
        if(limit!=null)
            selectQuery=selectQuery+" limit "+limit;
        LogProxy.d("SelectQuery: "+selectQuery);
        LogProxy.d("QueryParams: "+(params==null?"null":Arrays.asList(params)));

        Set<Word> result=new HashSet<>();
        Cursor cursor = readableDb.rawQuery(selectQuery, params);
        while(cursor.moveToNext()) {
            Word w=new Word(
                    getLong(cursor, 0),
                    getString(cursor, 1),
                    getLong(cursor, 2),
                    getLong(cursor, 3),
                    getBigDecimal(cursor, 4),
                    getLong(cursor, 5),
                    getBoolean(cursor, 6),
                    getDate(cursor, 7),
                    getDate(cursor, 8),
                    getDate(cursor,9),
                    getLong(cursor, 10),
                    getDate(cursor, 11)
            );
            result.add(w);
        }
        cursor.close();
        return(result);
    }

    private Boolean getBoolean(Cursor cursor, int columnIndex) {
        return cursor.isNull(columnIndex)?null:Boolean.valueOf(cursor.getInt(columnIndex)==1);
    }

    private Long getLong(Cursor cursor, int columnIndex) {
        return cursor.isNull(columnIndex)?null:new Long(cursor.getLong(columnIndex));
    }

    private BigDecimal getBigDecimal(Cursor cursor, int columnIndex) {
        return cursor.isNull(columnIndex)?null:new BigDecimal(cursor.getDouble(columnIndex));
    }

    private String getString(Cursor cursor, int columnIndex) {
        return cursor.isNull(columnIndex)?null:cursor.getString(columnIndex);
    }

    private Date getDate(Cursor cursor, int columnIndex) {
        return cursor.isNull(columnIndex)?null:new Date(cursor.getLong(columnIndex));
    }

    public static String createQuestionMarksList(int size) {
        StringBuffer result=new StringBuffer();
        for(int a=0;a<size;a++) {
            if(a>0)
                result.append(",");
            result.append("?");
        }
        return(result.toString());
    }

    public static String getStats(ForaMemoDbHelper dbHelper) {
        StringBuffer result=new StringBuffer();
        result.append(getStatsLine(dbHelper,WordGroupDef.getRepWords(),WordGroupDef.getRepMistakenWords(),WordGroupDef.getRepPassedWords()));
        result.append("/");
        result.append(getStatsLine(dbHelper,WordGroupDef.getNewWords(),WordGroupDef.getNewMistakenWords(),WordGroupDef.getNewPassedWords()));
        return(result.toString());
    }

    private static String getStatsLine(ForaMemoDbHelper dbHelper, WordGroupDef words, WordGroupDef mistakenWords, WordGroupDef passedWords) {
        StringBuffer result = new StringBuffer();
        result.append(words.getCount(dbHelper));
        result.append(":");
        result.append(mistakenWords.getCount(dbHelper));
        result.append("(");
        result.append(passedWords.getCount(dbHelper));
        result.append(")");
        return(result.toString());
    }


    public static abstract class FeedEntry implements BaseColumns {
        public static final String TABLE_NAME = "word";

        public static final String COLUMN_NAME_WORD = "word";
        public static final String COLUMN_NAME_R1 = "r1";
        public static final String COLUMN_NAME_R2 = "r2";
        public static final String COLUMN_NAME_EP = "ep";
        public static final String COLUMN_NAME_REP_NO = "rep_no";
        public static final String COLUMN_NAME_ENABLE = "enable";
        public static final String COLUMN_NAME_FIRST_OCCURRENCE = "first_occurrence";
        public static final String COLUMN_NAME_LAST_ANSWER = "last_answer";
        public static final String COLUMN_NAME_NEXT_OCCURRENCE = "next_occurrence";
        public static final String COLUMN_NAME_NEXT_CALCULATED_OCCURRENCE = "next_calculated_occurrence";
        public static final String COLUMN_NAME_ADDED = "added";
    }

    private static final String TEXT_TYPE = " TEXT";
    private static final String INTEGER_TYPE = " INTEGER";
    private static final String REAL_TYPE = " REAL";

    private static final String COMMA_SEP = ",";


    public ForaMemoDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        LogProxy.i("onCreate");
        String createSql = "CREATE TABLE " + FeedEntry.TABLE_NAME + " (" +
                FeedEntry._ID + " INTEGER PRIMARY KEY," +
                FeedEntry.COLUMN_NAME_WORD + TEXT_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_R1 + INTEGER_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_R2 + INTEGER_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_EP + REAL_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_REP_NO + INTEGER_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_ENABLE + INTEGER_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_FIRST_OCCURRENCE + INTEGER_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_LAST_ANSWER + INTEGER_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_NEXT_OCCURRENCE + INTEGER_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_NEXT_CALCULATED_OCCURRENCE + INTEGER_TYPE + COMMA_SEP +
                FeedEntry.COLUMN_NAME_ADDED + INTEGER_TYPE +
        " )";
        db.execSQL(createSql);
        db.execSQL("CREATE UNIQUE INDEX "+FeedEntry.COLUMN_NAME_WORD+"Unique ON "+FeedEntry.TABLE_NAME+"("+FeedEntry.COLUMN_NAME_WORD+")");

        //create index
        createIndex(db,ForaMemoDbHelper.FeedEntry.COLUMN_NAME_NEXT_OCCURRENCE);
        createIndex(db,ForaMemoDbHelper.FeedEntry.COLUMN_NAME_LAST_ANSWER);
        createIndex(db,ForaMemoDbHelper.FeedEntry.COLUMN_NAME_NEXT_CALCULATED_OCCURRENCE);
        createIndex(db,ForaMemoDbHelper.FeedEntry.COLUMN_NAME_FIRST_OCCURRENCE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        LogProxy.i("onUpgrade: "+oldVersion+"/"+newVersion);
        db.execSQL("DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME);
        onCreate(db);
    }

    public static void createIndex(SQLiteDatabase db,String column) {
        LogProxy.i("create indexes..."+column);
        db.execSQL("create index "+column+"_index on "+ForaMemoDbHelper.FeedEntry.TABLE_NAME+" ("+column+")");
    }
}
